/****** Object:  StoredProcedure [dbo].[AuthZ_spUpdateAuthZUserRole]    Script Date: 06/24/2009 23:48:02 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spUpdateAuthZUserRole]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spUpdateAuthZUserRole]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spUpdateAuthZUserRole]    Script Date: 06/24/2009 23:48:02 ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <8-4-2009>
-- Description:	<Deletes and rebuilds rows int AuthZUserRole table.  Table is a denormalized view of AuthZRoleMapping table.>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spUpdateAuthZUserRole]

	@alias NVARCHAR(50)

AS
BEGIN

	DECLARE @date AS DATETIME = GETUTCDATE()

	BEGIN TRY
		SET NOCOUNT ON;
	
		DECLARE @topRoles as table (AuthZUserID INT, RoleID INT, ParentRoleID int)
		INSERT INTO @topRoles

		/*Get all immediate roles associated to the user. */
		SELECT auser.AuthZUserID
		, role.RoleID
		, role.ParentRoleID
		FROM [dbo].[AuthZUser] auser
		JOIN [dbo].[AuthZUserRoleMapping] armap
		ON   armap.AuthZUserID = auser.AuthZUserID
		JOIN [dbo].[Role] role
		ON   role.RoleID = armap.RoleID
		
		WHILE @@ROWCOUNT > 0
		BEGIN

			INSERT INTO @topRoles

			/*Get the children of all roles children. */
			SELECT parent.AuthZUserID
			, child.RoleID
			, child.ParentRoleID
			FROM @topRoles parent
			JOIN [dbo].[Role] child
			ON   child.ParentRoleID = parent.RoleID  --Gets the children
			AND  not exists (           --Don't return rows that already exist.
			SELECT parent2.RoleID 
			FROM @topRoles parent2
			WHERE parent2.AuthZUserID = parent.AuthZUserID
			AND   parent2.RoleID = child.RoleID)
		END

		--  Delete all rows from AuthZUserRole table.
		DELETE FROM [dbo].[AuthZUserRole]
		
		--Insert all users and all their roles they are associated with.
		INSERT INTO [dbo].[AuthZUserRole]
		SELECT AuthZUserID
			, RoleID
			, @alias
			, @date
			, NULL
			, NULL
		FROM @topRoles

	END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
           
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH
END

GO

